﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace testGeometry.Data
{
    class DataLoader
    {
        private DbViewModel _dvm;

        public DataLoader(DbViewModel mvm)
        {
            this._dvm = mvm;
        }

        private void InitDb()
        {
            string strProvider = "Data Source=" + _dvm.host + ";Database=" + _dvm.database + ";User ID=" + _dvm.username + ";Password=" + _dvm.password;
            try
            {
                _dvm.DbConnection = new MySqlConnection(strProvider);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
        }






        public List<TriEvent> GetTriEvents(string queryText)
        {

            InitConnection();
            List<TriEvent> teList = new List<TriEvent>();
            MySqlCommand cmd = new MySqlCommand(queryText, myConn);
            MySqlDataReader mdr = cmd.ExecuteReader();
            while (mdr.Read())
            {
                try
                {
                    TriEvent te = new TriEvent(Double.Parse(mdr.GetDouble(0).ToString()), Double.Parse(mdr.GetDouble(1).ToString()), DateTime.Parse(mdr.GetDateTime(2).ToString()));
                    teList.Add(te);
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                }
            }
            return teList;
        }

        public object ExecuteQuery(string queryText)
        {
            InitConnection();
            MySqlCommand cmd = new MySqlCommand(queryText, myConn);
            return cmd.ExecuteScalar();
        }

        public WorkingSet ExtractParameters(string tableName, string latName, string lngName, string timeName)
        {
            WorkingSet workingSet = new WorkingSet();
            try
            {
                workingSet.MaxDbLat = Double.Parse(ExecuteQuery("select max(" + latName + ") from " + tableName).ToString());
                workingSet.MinDbLat = Double.Parse(ExecuteQuery("select min(if(" + latName + "=0,NULL," + latName + ")) from " + tableName).ToString());

                workingSet.MaxDbLng = Double.Parse(ExecuteQuery("select max(" + lngName + ") from " + tableName).ToString());
                workingSet.MinDbLng = Double.Parse(ExecuteQuery("select min(if(" + lngName + "=0,NULL," + lngName + ")) from " + tableName).ToString());

                workingSet.MaxDbDate = DateTime.Parse(ExecuteQuery("select max(" + timeName + ") from " + tableName).ToString());
                workingSet.MinDbDate = DateTime.Parse(ExecuteQuery("select min(if(" + timeName + "=0,NULL," + timeName + ")) from " + tableName).ToString());

                workingSet.TotalRows = int.Parse(ExecuteQuery("select count(*) from" + tableName).ToString());

                workingSet.TriEventList = GetTriEvents("select " + latName + "," + lngName + "," + timeName + " from " + tableName + " where " + latName + " IS NOT NULL AND " + lngName + " IS NOT NULL AND " + timeName + " IS NOT NULL");
                return workingSet;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return null;
        }

        public WorkingSet ExtractParameters()
        {
            string tableName = TriGraphResources.tableName;
            string latName = TriGraphResources.latName;
            string lngName = TriGraphResources.lngName;
            string timeName = TriGraphResources.timeName;

            WorkingSet workingSet = new WorkingSet();
            try
            {
                workingSet.MaxDbLat = Double.Parse(ExecuteQuery("select max(" + latName + ") from " + tableName).ToString());
                workingSet.MinDbLat = Double.Parse(ExecuteQuery("select min(if(" + latName + "=0,NULL," + latName + ")) from " + tableName).ToString());

                workingSet.MaxDbLng = Double.Parse(ExecuteQuery("select max(" + lngName + ") from " + tableName).ToString());
                workingSet.MinDbLng = Double.Parse(ExecuteQuery("select min(if(" + lngName + "=0,NULL," + lngName + ")) from " + tableName).ToString());

                workingSet.MaxDbDate = DateTime.Parse(ExecuteQuery("select max(" + timeName + ") from " + tableName).ToString());
                workingSet.MinDbDate = DateTime.Parse(ExecuteQuery("select min(if(" + timeName + "=0,NULL," + timeName + ")) from " + tableName).ToString());

                workingSet.TotalRows = int.Parse(ExecuteQuery("select count(*) from" + tableName).ToString());

                workingSet.TriEventList = GetTriEvents("select " + latName + "," + lngName + "," + timeName + " from " + tableName + " where " + latName + " IS NOT NULL AND " + lngName + " IS NOT NULL AND " + timeName + " IS NOT NULL");
                return workingSet;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return null;
        }

        public WorkingSet ExtractParameters(string condition)
        {
            if (null == condition) return ExtractParameters();

            string tableName = TriGraphResources.tableName;
            string latName = TriGraphResources.latName;
            string lngName = TriGraphResources.lngName;
            string timeName = TriGraphResources.timeName;

            WorkingSet workingSet = new WorkingSet();
            try
            {
                workingSet.MaxDbLat = Double.Parse(ExecuteQuery("select max(" + latName + ") from " + tableName + " where " + condition).ToString());
                workingSet.MinDbLat = Double.Parse(ExecuteQuery("select min(if(" + latName + "=0,NULL," + latName + ")) from " + tableName + " where " + condition).ToString());

                workingSet.MaxDbLng = Double.Parse(ExecuteQuery("select max(" + lngName + ") from " + tableName + " where " + condition).ToString());
                workingSet.MinDbLng = Double.Parse(ExecuteQuery("select min(if(" + lngName + "=0,NULL," + lngName + ")) from " + tableName + " where " + condition).ToString());

                workingSet.MaxDbDate = DateTime.Parse(ExecuteQuery("select max(" + timeName + ") from " + tableName + " where " + condition).ToString());
                workingSet.MinDbDate = DateTime.Parse(ExecuteQuery("select min(if(" + timeName + "=0,NULL," + timeName + ")) from " + tableName + " where " + condition).ToString());

                workingSet.TotalRows = int.Parse(ExecuteQuery("select count(*) from " + tableName + " where " + condition).ToString());

                workingSet.TriEventList = GetTriEvents("select " + latName + "," + lngName + "," + timeName + " from " + tableName + " where " + latName + " IS NOT NULL AND " + lngName + " IS NOT NULL AND " + timeName + " IS NOT NULL AND " + condition);
                return workingSet;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return null;
        }

        public bool CheckIfClustersExist()
        {
            InitConnection();
            if ((int)ExecuteQuery("select count(*) from information_schema.tables where table_name='" + TriGraphResources.baseName + "_cluster_" + TriGraphResources.DEFAULT_ZOOM_LEVEL + "m'") == 0) return false;
            else return true;
        }

        public WorkingSet ExtractClusterParameters()
        {
            string tableName = TriGraphResources.baseName + "_cluster_" + TriGraphResources.DEFAULT_ZOOM_LEVEL + "m";
            string latName = TriGraphResources.latName;
            string lngName = TriGraphResources.lngName;
            string timeName = TriGraphResources.timeName;

            WorkingSet workingSet = new WorkingSet();
            try
            {
                workingSet.MaxDbLat = Double.Parse(ExecuteQuery("select max(" + latName + ") from " + tableName).ToString());
                workingSet.MinDbLat = Double.Parse(ExecuteQuery("select min(if(" + latName + "=0,NULL," + latName + ")) from " + tableName).ToString());

                workingSet.MaxDbLng = Double.Parse(ExecuteQuery("select max(" + lngName + ") from " + tableName).ToString());
                workingSet.MinDbLng = Double.Parse(ExecuteQuery("select min(if(" + lngName + "=0,NULL," + lngName + ")) from " + tableName).ToString());

                workingSet.MaxDbDate = DateTime.Parse(ExecuteQuery("select max(" + timeName + ") from " + tableName).ToString());
                workingSet.MinDbDate = DateTime.Parse(ExecuteQuery("select min(if(" + timeName + "=0,NULL," + timeName + ")) from " + tableName).ToString());

                workingSet.TotalRows = int.Parse(ExecuteQuery("select count(*) from" + tableName).ToString());

                workingSet.TriEventList = GetTriEvents("select max(" + latName + "),max(" + lngName + "),max(" + timeName + ") from " + tableName + " group by cluster_id");
                return workingSet;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            return null;
        }
    }
}

